18-3 洏 SQL 佽

我們在上一節介紹了如何使用 ASP 來讀取資料庫中的資料,並將其呈現於網頁中。ASP 與資料庫溝通的標準語言就是 SQL,經由此種語言,我們才能在 ASP 程式碼對資料庫進行檢視、新增、修改、刪除等動作。本節將對 SQL 進一步介紹其用法。

SQL 是「結構化查詢語言」(Structured Query Language)的簡稱,是由 IBM 公司於 1970 年代所發展出來,用於關聯式資料庫 (Relational Databases) 當中的一種資料庫查詢語言,利用 SQL 可以用來進行各種與資料庫相關的處理,例如:

由於 SQL 功能定義完整,所以已經成為一個查詢資料庫的標準語言。雖然各家資料庫所提供的 SQL 語言在功能上會略有差異,但基本的功能是一致的。本單元將 ASP 與資料庫整合最常用到的SQL語法做一個整理,由於 SQL 的語法相當平易近人,所以讀者只要對以下的語法稍做了解之後,即可馬上進入 ASP 與資料庫整合的世界。

任何資料庫都有四個基本查詢動作,即檢視、新增、修改、刪除,以下將逐一介紹這四種基本功能的 SQL 語法,以及相關的範例。

若要檢視資料庫的資料,使用的 SQL 主要指令是「SELECT」,基本語法如下:

SELECT 欄位名稱1, 欄位名稱2, ... FROM 資料表名稱1, 資料表名稱2, ... [WHERE 條件式] [ORDER BY 欄位名稱1, 欄位名稱2, ...] 說明如下: SQL 的設計基本上是模仿英文的自然語法,因此在入門上較為容易。接著我們來看看幾個範例,就可以瞭解 SQL 的精髓。

首先,我們以資料庫 asr/example/database/basketball.mdb 為例,這個資料庫包含兩個資料表:

相關內容如下:
資料庫 "example/database/basketball.mdb"
資料表 "Player" 的內容 資料表 "Team" 的內容
ID NickName Name TeamID Percentage
jean 吳志銘 38.25 
jones 張秤嘉 49.77 
ben 陳孜彬 50.26 
asser 林惠娟 37.22 
window 李宜揚 36.67 
roger 張智星 25.88 
cosh 許文豪 70.98 
banny 洪鵬翔 88.97 
shyba 邱中人 67.45 
10 batty 楊璧如 65.55 
11 joey 許嘉晉 47.65 
12 roland 吳瑞千 55.87 
13 sony 林頌華 54.77 
14 beball 葉佳慧 33.33 
15 gavins 林政源 55.65 
16 jojo 陳俊傑 44.65 
17 jtchen 陳江村 48.76 
18 Gao 高名揚 67.88 
19 Wayne 陳智偉 65.87 
20 chingz 陳晴 57.28 
ID Name WinNo
台北隊 12 
新竹隊 
台中隊 10 
南投隊 12 
台南隊 17 
高雄隊 16 
澎湖隊 11 

以下是幾個檢視指令的基本範例,都只有牽涉到單一資料表。(讀者可以只看 SQL 指令,猜猜它的意義,再看看中文解譯。)

  1. SQL指令:SELECT * FROM Team
    意義:所有球隊資料
    說明:「*」代表 Team 資料表中所有的欄位。
    查詢結果:
    ID Name WinNo
    台北隊 12 
    新竹隊 
    台中隊 10 
    南投隊 12 
    台南隊 17 
    高雄隊 16 
    澎湖隊 11 
  2. SQL指令:SELECT TOP 3 * FROM Team
    意義:所有球隊資料,但只抓前三筆
    說明:「TOP 3」代表只抓取前三筆資料。也可以使用「TOP 25 percent」等,代表抓取所有資料的前百分之二十五。
    查詢結果:
    ID Name WinNo
    台北隊 12 
    新竹隊 
    台中隊 10 
  3. SQL指令:SELECT Name, Percentage FROM Player WHERE NickName='gavins'
    意義:綽號為 gavins 的球員姓名及命中率
    查詢結果:
    Name Percentage
    林政源 55.65 
  4. SQL指令:SELECT * FROM Team WHERE Name like '台%'
    意義:隊名以「台」開頭的球隊資料
    說明:「%」代表任意長度的字串。
    查詢結果:
    ID Name WinNo
    台北隊 12 
    台中隊 10 
    台南隊 17 
  5. SQL指令:SELECT Name, Percentage FROM Player WHERE Name like '陳__'
    意義:「姓陳且名字有三個字」的球員姓名及命中率
    說明:「_」代表任意單一字元。
    查詢結果:
    Name Percentage
    陳孜彬 50.26 
    陳俊傑 44.65 
    陳江村 48.76 
    陳智偉 65.87 
  6. SQL指令:SELECT Name, WinNo FROM Team WHERE WinNo>10
    意義:「勝場數大於10」的球隊名稱及其勝場數
    查詢結果:
    Name WinNo
    台北隊 12 
    南投隊 12 
    台南隊 17 
    高雄隊 16 
    澎湖隊 11 
  7. SQL指令:SELECT Name, WinNo FROM Team WHERE WinNo>10 ORDER BY WinNo DESC
    意義:「勝場數大於10」的球隊名稱及其勝場數,並根據勝場數由大到小排列
    說明:若不加入 DESC,則會進行由小到大的排序。
    查詢結果:
    Name WinNo
    台南隊 17 
    高雄隊 16 
    南投隊 12 
    台北隊 12 
    澎湖隊 11 
  8. SQL指令:SELECT TeamID, Name, Percentage FROM Player WHERE TeamID=5 ORDER BY Percentage DESC
    意義:「球隊代碼為5」的球員命中率排行榜
    查詢結果:
    TeamID Name Percentage
    邱中人 67.45 
    陳晴 57.28 
    林政源 55.65 
    張秤嘉 49.77 
    陳俊傑 44.65 
    葉佳慧 33.33 
  9. SQL指令:SELECT * FROM Player ORDER BY TeamID, Percentage DESC
    意義:每一隊的球員命中率排行榜
    說明:列出結果會先按 TeamID 由小到大排序,再按 Percentage 由大到小排序。
    查詢結果:
    ID NickName Name TeamID Percentage
    18 Gao 高名揚 67.88 
    12 roland 吳瑞千 55.87 
    13 sony 林頌華 54.77 
    ben 陳孜彬 50.26 
    jean 吳志銘 38.25 
    window 李宜揚 36.67 
    roger 張智星 25.88 
    17 jtchen 陳江村 48.76 
    11 joey 許嘉晉 47.65 
    asser 林惠娟 37.22 
    10 batty 楊璧如 65.55 
    shyba 邱中人 67.45 
    20 chingz 陳晴 57.28 
    15 gavins 林政源 55.65 
    jones 張秤嘉 49.77 
    16 jojo 陳俊傑 44.65 
    14 beball 葉佳慧 33.33 
    banny 洪鵬翔 88.97 
    cosh 許文豪 70.98 
    19 Wayne 陳智偉 65.87 
  10. SQL指令:SELECT count(*) FROM Team WHERE WinNo>10
    意義:「勝場數大於10」的球隊總數
    說明:count()函數會計算資料筆數,資料庫會自動產生暫時的欄位名稱 Expr1000。
    查詢結果:
    Expr1000
  11. SQL指令:SELECT max(Percentage) as 最高命中率 FROM Player
    意義:所有球員的最高命中率
    說明:max(Percentage)函數會計算命中率最大值。由於使用了「as 最高命中率」,資料庫會自動產生暫時的欄位名稱「最高命中率」。
    查詢結果:
    最高命中率
    88.97 
  12. SQL指令:SELECT TOP 1 Name, Percentage FROM Player ORDER BY Percentage DESC
    意義:具有最高命中率的球員資料
    查詢結果:
    Name Percentage
    洪鵬翔 88.97 
  13. SQL指令:SELECT Name, Percentage FROM Player WHERE Percentage in (SELECT max(Percentage) FROM Player)
    意義:具有最高命中率的球員資料
    說明:功能同前一個範例,但是改用兩個 SQL 指令組合來達成同樣的效果。
    查詢結果:
    Name Percentage
    洪鵬翔 88.97 
在上述範例中,我們已經知道如何使用 count 或是 max 函數來進行統計,但若要根據欄位值不同來進行統計,就必須用到群組指令「GROUP BY」,若還要指定相關條件,就必須用到群組條件「HAVING」,因此 SQL 所使用的 SELECT 指令格式就會比較複雜,如下: SELECT 欄位名稱1, 欄位名稱2, ... FROM 資料表名稱1, 資料表名稱2, ... [WHERE 條件式] [GROUP BY 欄位名稱1, 欄位名稱2, ...] [HAVING 條件式] [ORDER BY 欄位名稱1, 欄位名稱2, ...] 我們針對新增的敘述來說明如下: 以下是幾個使用到 GROUP BY 及 HAVING 的 SQL 範例:
  1. SQL指令:SELECT TeamID, count(*) as 球員人數, avg(Percentage) as 平均命中率 FROM Player GROUP BY TeamID
    意義:每個球隊的球員人數及平均命中率
    說明:avg(Percentage) 可以計算命中率平均值,類似的 SQL 聚合函數有 Avg(平均值)、Count(筆數)、Max(最大值)、Min(最小值)、StDev(母群體樣本標準差)、StDevp(母群體標準差)、Sum(總和)、Var(母群體樣本變異數)、VarP(母群體變異數)等。由於這是對於每個球隊的統計數字,所以必須用到群組指令「GROUP BY」。
    查詢結果:
    TeamID 球員人數 平均命中率
    50.61666666666667 
    25.88 
    44.54333333333333 
    65.55 
    51.355 
    79.975 
    65.87 
  2. SQL指令:SELECT TeamID, count(*) as 球員人數 FROM Player GROUP BY TeamID HAVING count(*)>2
    意義:每個球隊的球員人數,但只顯示球員人數大於 2 位的資料
    說明:avg(Percentage) 可以計算命中率平均值。由於這是對於每個球隊的統計數字,所以必須用到群組指令「GROUP BY」,相關的條件則必須使用「HAVING」來指定。
    查詢結果:
    TeamID 球員人數

以上範例都是只有針對一個資料表來進行檢視查詢,下列的範例則是根據這兩個資料表的關聯性來進行檢視查詢。

  1. SQL指令:SELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE ((Team.Name='台北隊') and (Player.TeamID=Team.ID))
    意義:台北隊的球員資料
    說明:由於兩個資料表都有 Name 欄位,所以我們必須使用 Team.Name 及 Player.Name 來區分不同資料表的欄位。另外,這兩個資料表的關聯性是由(Player.TeamID=Team.ID) 所建立,所以在後續的範例中,我們會不斷使用這個查詢條件。
    查詢結果:
    Name Name Percentage
    台北隊 陳孜彬 50.26 
    台北隊 高名揚 67.88 
    台北隊 李宜揚 36.67 
    台北隊 林頌華 54.77 
    台北隊 吳瑞千 55.87 
    台北隊 吳志銘 38.25 
  2. SQL指令:SELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE (Player.TeamID=Team.ID) and (Team.Name IN ('高雄隊', '台中隊')) ORDER BY Team.Name, Percentage DESC
    意義:高雄隊和台中隊的射手排行榜
    查詢結果:
    Name Name Percentage
    台中隊 陳江村 48.76 
    台中隊 許嘉晉 47.65 
    台中隊 林惠娟 37.22 
    高雄隊 洪鵬翔 88.97 
    高雄隊 許文豪 70.98 
  3. SQL指令:SELECT Team.Name as 球隊名稱, Team.WinNo as 贏場次數, count(*) as 球員人數, max(Percentage) as 最高命中率, min(Percentage) as 最低命中率, avg(Percentage) as 平均命中率 FROM Player, Team WHERE ((Player.TeamID=Team.ID)) GROUP BY Team.Name, Team.WinNo
    意義:每個球隊的相關統計數字
    說明:由於這是對於每個球隊的統計數字,所以必須用到群組指令「GROUP BY」。同時由於被選取的欄位中,Team.Name 和 Team.WinNo 都沒有用到任何聚合函數,所以在 GROUP BY 之後也必須要加上這兩個欄位。
    查詢結果:
    球隊名稱 贏場次數 球員人數 最高命中率 最低命中率 平均命中率
    台中隊 10 48.76 37.22 44.54333333333333 
    台北隊 12 67.88 36.67 50.61666666666667 
    台南隊 17 67.45 33.33 51.355 
    南投隊 12 65.55 65.55 65.55 
    高雄隊 16 88.97 70.98 79.975 
    新竹隊 25.88 25.88 25.88 
    澎湖隊 11 65.87 65.87 65.87 

另外還有一些使用 SQL 來檢視資料的範例,可見 asp/example/database/selectQuery01.asp,請讀者自行試看看。

下一小節將說明如何使用 SQL 指令來對資料庫進行新增、修改、刪除資料等動作。


JScript 程式設計與應用:用於伺服器端的 ASP 環境